package pbc.gx.ekp.addressbook.dao;

import com.fasterxml.jackson.core.sym.NameN;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import pbc.gx.ekp.addressbook.utils.OrgIds;
import pbc.gx.ekp.addressbook.utils.SecurityUtils;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
@Slf4j
public class DeptDaoImpl implements DeptDao {


    @Autowired
    private OrgIds orgIds;

    private static final String deptOrderTable = "sys_org_dept_order";

    private final JdbcTemplate jdbcTemplate;  //这个是系统自带的

    public DeptDaoImpl(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /*
        private String getUserOrgPath(String userId) {
            String rootID=orgIds.getMap().get("rootId");
            String nanId=orgIds.getMap().get("nanId");
            String sql = " select d.fdId as fdId,d.fdName as fdName,d.fdParentId as orgPath " +
                    " from sys_org_element p join " + deptOrderTable + " d on p.fd_parentorgid=d.fdId " +
                    " join sys_org_person p2 on p.fd_id = p2.fd_id" +
                    " where p2.fd_login_name=? and p.fd_org_type=8 ";

            List<Map<String, String>> orgs = jdbcTemplate.query(sql, (rs, rowNum) -> {
                Map<String, String> dept = new HashMap<>();
                dept.put("fdId", rs.getString("fdId"));
                dept.put("fdName", rs.getString("fdName"));
                dept.put("orgPath", rs.getString("orgPath"));
                return dept;
            }, userId);
            if (orgs.size() > 0) {
                Map<String, String> org = orgs.get(0);
                if (org.get("fdId").equals(nanId)) {
                    return rootID;
                }
                String orgPath = org.get("orgPath");
                String orgName = org.get("fdName");
                if (orgName.endsWith("市中心支行")) {
                    return orgPath;
                }
                if (orgName.endsWith("支行")) {
                    if (orgPath.startsWith(rootID + "," + nanId)) {
                        return orgPath;
                    }
                    return orgPath.substring(0, orgPath.lastIndexOf(','));
                }
            }
            return "";
        }

        private String getOrgPath(String orgId) {
            String rootID=orgIds.getMap().get("rootId");
            String nanId=orgIds.getMap().get("nanId");
            String sql = " select d.fdId as fdId,d.fdName as fdName,d.fdParentId as orgPath " +
                    " from " + deptOrderTable + " d where d.fdId=? ";

            List<Map<String, String>> orgs = jdbcTemplate.query(sql, (rs, rowNum) -> {
                Map<String, String> dept = new HashMap<>();
                dept.put("fdId", rs.getString("fdId"));
                dept.put("fdName", rs.getString("fdName"));
                dept.put("orgPath", rs.getString("orgPath"));
                return dept;
            }, orgId);
            if (orgs.size() > 0) {
                Map<String, String> org = orgs.get(0);
                if (org.get("fdId").equals(nanId)) {
                    return rootID;
                }
                String orgPath = org.get("orgPath");
                String orgName = org.get("fdName");
                if (orgName.endsWith("市中心支行")) {
                    return orgPath;
                }
                if (orgName.endsWith("支行")) {
                    if (orgPath.startsWith(rootID + "," + nanId)) {
                        return orgPath;
                    }
                    return orgPath.substring(0, orgPath.lastIndexOf(','));
                }
            }
            return "";
        }
    */
    @Override
    public List<Map<String, Object>> getDepts(String deptID) {
        String rootID = orgIds.getMap().get("rootId");
        String nanId = orgIds.getMap().get("nanId");
        String whereDept = " fd_parentid = ?  ";
        if (StringUtils.isBlank(deptID)) {
            if (SecurityUtils.getOrgId().equals(nanId)) {
                deptID = rootID;
            } else {
                deptID = SecurityUtils.getOrgId();
                whereDept = " fd_id = ?  ";
            }
        }

        String sql = " SELECT	"
                + " fd_id AS id, "
                + " fd_name AS name, "
                + " ( SELECT COUNT(*) FROM sys_org_element WHERE fd_parentid = d.fd_id AND fd_org_type IN ( 1, 2 )) AS isLeaf"
                + " FROM "
                + " 	sys_org_element d "
                + " WHERE "
                + " 	fd_is_available = 1 "
                + " 	AND fd_org_type IN ( 1, 2 ) "
                + " 	AND  " + whereDept
                + " ORDER BY fd_org_type desc,fd_order asc";
        return jdbcTemplate.query(sql, (rs, rowNum) -> {
            Map<String, Object> dept = new HashMap<>();
            dept.put("id", rs.getObject("id"));
            dept.put("name", rs.getObject("name"));
            dept.put("isLeaf", ((long) rs.getObject("isLeaf")) <= 0);
            return dept;
        }, deptID);
    }


    @Override
    @Transactional
    public Map<String, Object> getPersons(String departmentId, String department, String name, String phone, int pageNum, int pageSize) {
        //long startTime = System.currentTimeMillis();

        String rootID = orgIds.getMap().get("rootId");
        String nanId = orgIds.getMap().get("nanId");
        Map<String, Object> rsMap = new HashMap<>();
        /*
        String orgPath = getUserOrgPath(SecurityUtils.getUserId());
        String orgPath = getOrgPath(SecurityUtils.getOrgId());
        //log.info("orgPath:" + orgPath);
        if (StringUtils.isBlank(orgPath)) {
            rsMap.put("total", 0);
            rsMap.put("personArr", new ArrayList<>());
            return rsMap;
        }
        */
        NamedParameterJdbcTemplate jdbc = new NamedParameterJdbcTemplate(jdbcTemplate);

        MapSqlParameterSource param = new MapSqlParameterSource();
        String selectCount = "select count(*) as total  ";
        String select = "SELECT"
                + "	 e.fd_id AS id, "
                + "	 e.fd_name AS name, "
                + "	 d.fdName AS department, "
                + "  p.fd_sex AS sex, "
                //+ "	 if(d.fdParentId like :orgPath ,p.fd_mobile_no,'') AS cellPhone, " //手机号过滤
                //+ "	 if(e.fd_memo regexp '[1][0-9]{10}$',concat(p.fd_mobile_no,',',e.fd_memo),p.fd_mobile_no) AS cellPhone, "
                + "  concat(if(length(p.fd_mobile_no),p.fd_mobile_no,''),if(length(p.fd_mobile_no) and length(p.fd_telephone_alternate),',',''),if(length(p.fd_telephone_alternate),p.fd_telephone_alternate,'')) AS cellPhone, "
                + "	 p.fd_short_no AS interPhone, "
                + "	 p.fd_work_phone AS externalLine, "
                + "	 p.fd_email AS email ";
        String from = " FROM "
                + " " + deptOrderTable + " d join sys_org_element e on e.fd_parentid = d.fdId "
                + " left join sys_org_person p on e.fd_id = p.fd_id "
                + " WHERE  "
                + " find_in_set(:departmentId,d.fdparentId) and e.fd_is_available = 1 and e.fd_org_type = 8 ";
        //param.addValue("orgPath", orgPath + "%");
        param.addValue("departmentId", departmentId);
        String where = "";
        if (StringUtils.isNotBlank(department)) {
            where += " AND (d.fdName like :department) ";
            param.addValue("department", "%" + department + "%");
        }
        if (StringUtils.isNotBlank(name)) {
            where += " AND e.fd_name like :name ";
            param.addValue("name", "%" + name + "%");
        }
        if (StringUtils.isNotBlank(phone)) {
            //where += " AND ((d.fdParentId like :orgPath and p.fd_mobile_no like :phone) or p.fd_work_phone like :phone or p.fd_short_no like :phone) ";
            where += " AND ( p.fd_mobile_no like :phone or p.fd_work_phone like :phone or p.fd_short_no like :phone or p.fd_telephone_alternate like :phone) ";
            param.addValue("phone", "%" + phone + "%");
        }
        Map<String, Object> rs = jdbc.queryForMap(selectCount + from + where, param);
        long total = (long) rs.get("total");
        String order = " order by  if(d.fdType=1||d.fdParentId=concat(:rootId,',',:nnzzId,',',d.fdId),concat(d.fdOrder,'999999'),d.fdOrder) asc , CONVERT(d.fdName USING gbk) asc, if(isnull(e.fd_order),999999,e.fd_order) asc,CONVERT(e.fd_name USING gbk) asc LIMIT :start,:pageSize ";
        param.addValue("rootId", rootID);
        param.addValue("nnzzId", nanId);
        int start = 0;
        if (pageNum > 1) {
            start = (pageNum - 1) * pageSize;
        }
        param.addValue("start", start);
        param.addValue("pageSize", pageSize);
        List<Map<String, Object>> persons = jdbc.query(select + from + where + order, param, (rs1, rowNum) -> {
            Map<String, Object> dept = new HashMap<>();
            dept.put("id", rs1.getObject("id"));
            dept.put("name", rs1.getObject("name"));
            dept.put("cellPhone", rs1.getObject("cellPhone"));
            dept.put("interPhone", rs1.getObject("interPhone"));
            dept.put("externalLine", rs1.getObject("externalLine"));
            dept.put("email", rs1.getObject("email"));
            dept.put("department", rs1.getObject("department"));
            String sex = rs1.getString("sex");
            dept.put("sex", (StringUtils.isNotBlank(sex) && sex.equals("M")) ? 1 : 0);
            return dept;
        });
        rsMap.put("total", total);
        rsMap.put("personArr", persons);
        return rsMap;
    }

    @Override
    @Transactional
    public List getDownloadPersons(String departmentId, String department, String name, String phone) {
        //long startTime = System.currentTimeMillis();
        String rootID = orgIds.getMap().get("rootId");
        String nanId = orgIds.getMap().get("nanId");
        /*
        //String orgPath = getUserOrgPath(SecurityUtils.getUserId());
        String orgPath = getOrgPath(SecurityUtils.getOrgId());
        //log.info("orgPath:" + orgPath);
        if (StringUtils.isBlank(orgPath)) {
            return new ArrayList<>();
        }
         */
        NamedParameterJdbcTemplate jdbc = new NamedParameterJdbcTemplate(jdbcTemplate);

        MapSqlParameterSource param = new MapSqlParameterSource();
        String selectCount = "select count(*) as total  ";
        String select = "SELECT"
                + "	 e.fd_id AS id, "
                + "	 e.fd_name AS name, "
                + "	 d.fdName AS department, "
                + "  p.fd_sex AS sex, "
                //+ "	 if(d.fdParentId like :orgPath ,p.fd_mobile_no,'') AS cellPhone, " //手机号过滤
                //+ "	 if(e.fd_memo regexp '[1][0-9]{10}$',concat(p.fd_mobile_no,',',e.fd_memo),p.fd_mobile_no) AS cellPhone, "
                + "  concat(if(length(p.fd_mobile_no),p.fd_mobile_no,''),if(length(p.fd_mobile_no) and length(p.fd_telephone_alternate),',',''),if(length(p.fd_telephone_alternate),p.fd_telephone_alternate,'')) AS cellPhone, "
                + "	 p.fd_mobile_no AS cellPhone, "
                + "	 p.fd_short_no AS interPhone, "
                + "	 p.fd_work_phone AS externalLine, "
                + "	 p.fd_email AS email ";
        String from = " FROM "
                + " " + deptOrderTable + " d join sys_org_element e on e.fd_parentid = d.fdId "
                + " left join sys_org_person p on e.fd_id = p.fd_id "
                + " WHERE  "
                + " find_in_set(:departmentId,d.fdparentId) and e.fd_is_available = 1 and e.fd_org_type = 8 ";
        //param.addValue("orgPath", orgPath + "%");
        param.addValue("departmentId", departmentId);
        String where = "";
        if (StringUtils.isNotBlank(department)) {
            where += " AND (d.fdName like :department) ";
            param.addValue("department", "%" + department + "%");
        }
        if (StringUtils.isNotBlank(name)) {
            where += " AND e.fd_name like :name ";
            param.addValue("name", "%" + name + "%");
        }
        if (StringUtils.isNotBlank(phone)) {
            //where += " AND ((d.fdParentId like :orgPath and p.fd_mobile_no like :phone) or p.fd_work_phone like :phone or p.fd_short_no like :phone) ";
            where += " AND ( p.fd_mobile_no like :phone or p.fd_work_phone like :phone or p.fd_short_no like :phone or p.fd_telephone_alternate like :phone) ";
            param.addValue("phone", "%" + phone + "%");
        }
        Map<String, Object> rs = jdbc.queryForMap(selectCount + from + where, param);
        long total = (long) rs.get("total");
        String order = " order by  if(d.fdType=1||d.fdParentId=concat(:rootId,',',:nnzzId,',',d.fdId),concat(d.fdOrder,'999999'),d.fdOrder) asc , CONVERT(d.fdName USING gbk) asc, if(isnull(e.fd_order),999999,e.fd_order) asc,CONVERT(e.fd_name USING gbk) asc ";
        param.addValue("rootId", rootID);
        param.addValue("nnzzId", nanId);
        return jdbc.query(select + from + where + order, param, (rs1, rowNum) -> {
            List dept = new ArrayList();
            dept.add(rowNum + 1);
            dept.add(rs1.getObject("name"));
            dept.add(rs1.getObject("department"));
            dept.add(rs1.getObject("interPhone"));
            dept.add(rs1.getObject("externalLine"));
            dept.add(rs1.getObject("cellPhone"));
            dept.add(rs1.getObject("email"));
            return dept;
        });
    }

    @Override
    @Transactional
    public void updateDeptOrder() {
        String rootID = orgIds.getMap().get("rootId");
        NamedParameterJdbcTemplate jdbc = new NamedParameterJdbcTemplate(jdbcTemplate);
        jdbcTemplate.update("CREATE TABLE  If Not Exists " + deptOrderTable + " (fdId varchar(100) primary key,fdType int,fdName varchar(500),fdParentId varchar(2048),fdOrder varchar(200),fdLevel INTEGER," +
                "index fdOrder (fdOrder)) ENGINE = MEMORY");
        jdbcTemplate.update("delete from " + deptOrderTable);
        int currentLevel = 1;
        Map<String, Object> rootMap = new HashMap<>();
        rootMap.put("fdId", rootID);
        rootMap.put("fdType", 1);
        rootMap.put("fdName", "");
        rootMap.put("fdOrder", "");
        rootMap.put("fdLevel", currentLevel);
        rootMap.put("fdParentId", rootID);
        jdbc.update("insert into " + deptOrderTable + " (fdId,fdType,fdName,fdParentId,fdOrder,fdLevel) values (:fdId,:fdType,:fdName,:fdParentId,:fdOrder,:fdLevel)", rootMap);
        while (true) {
            String sql = " insert into " + deptOrderTable + "( " +
                    " select e.fd_id as fdId,e.fd_org_type as fdType,concat(d.fdName,'/',e.fd_name) as fdName,concat(d.fdParentId,',',e.fd_id) as fdParentId, " +
                    "concat(d.fdOrder,if(e.fd_org_type=2,1,2),if(isnull(e.fd_order),'999999',LPAD(e.fd_order,6,'0'))) as fdOrder,:fdLevel2 as fdLevel " +
                    " from " + deptOrderTable + " d join sys_org_element e on e.fd_parentid=d.fdId  " +
                    " where e.fd_parentid in (select fdID from " + deptOrderTable + " where fdLevel=:fdLevel)  and e.fd_org_type in (1,2) ) ";
            MapSqlParameterSource paramDept = new MapSqlParameterSource();
            paramDept.addValue("fdLevel2", currentLevel + 1);
            paramDept.addValue("fdLevel", currentLevel);
            int rs = jdbc.update(sql, paramDept);
            if (rs <= 0) {
                break;
            }
            currentLevel++;
        }
    }

    @Override
    public Map<String, Object> getPersons(String department, String name, String phone, int pageNum, int pageSize) {
        String rootID = orgIds.getMap().get("rootId");
        String nanId = orgIds.getMap().get("nanId");
        return this.getPersons(SecurityUtils.getOrgId().equals(nanId) ? rootID : SecurityUtils.getOrgId(), department, name, phone, pageNum, pageSize);
    }

    @Override
    public List getDownloadPersons(String department, String name, String phone) {
        String rootID = orgIds.getMap().get("rootId");
        String nanId = orgIds.getMap().get("nanId");
        return this.getDownloadPersons(SecurityUtils.getOrgId().equals(nanId) ? rootID : SecurityUtils.getOrgId(), department, name, phone);
    }
}
